package cn.com.headfree.mouldshow.dao.impl;

import cn.com.headfree.entity.*;
import cn.com.headfree.mouldshow.dao.ExportWordDao;
import cn.com.headfree.vo.complex.THonorInfoVo;
import cn.com.headfree.vo.complex.TPatentInfoVo;
import cn.com.headfree.vo.complex.TTecInnovInfoVo;
import cn.com.headfree.vo.complex.TTecManInnovVo;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("/exportWordDao")
public class ExportWordDaoImpl extends BaseDaoImpl<UserInfoWordVo> implements ExportWordDao {

    /**
     * 查询基础信息
     * @param erpNo
     * @return
     */
    @Override
    public UserInfoWordVo getUserInfo(String erpNo){
        StringBuilder sb = new StringBuilder("SELECT tebi.id AS ID, tebi.name AS NAME, tebi.sex AS SEX, tebi.birth_date AS BIRTH_DATE, tebi.nation AS NATION, tebi.born_place AS BORN_PLACE,");
        sb.append(" tebi.politi_status AS POLITI_STATUS, tebi.id_card_no AS ID_CARD_NO, tebi.mobile_phone AS MOBILE_PHONE, tebi.birth_place AS BIRTH_PLACE, tebi.party_date AS PARTY_DATE, tebi.job_date AS JOB_DATE, tebi.cur_post AS CUR_POST");
        sb.append(" FROM t_emp_base_info tebi WHERE tebi.erp_no = ? AND tebi.del_sign = 0 AND tebi.is_valid = 0 ");
        return this.findUnique(UserInfoWordVo.class, sb.toString(), new Object[] { erpNo });
    }

    /**
     * 查询学历
     * @param erpNo
     * @return
     */
    @Override
    public List<TEducateInfo> getEducateInfoList(String erpNo, String type){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT tei.id AS ID, tei.start_time AS START_TIME, tei.end_time AS END_TIME, tei.grad_school AS GRAD_SCHOOL, tei.educ_name AS EDUC_NAME, tei.degree_name AS DEGREE_NAME,tei.major ");
        sqlbuilder.append(" FROM t_educate_info tei WHERE tei.erp_no = ? AND tei.is_cont_educ =? AND tei.del_sign = 0 AND tei.is_valid = 0 ORDER BY tei.start_time asc");
        return this.findAllBySQL(TEducateInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo,type});
    }

    /**
     * 专业技术职务
     * @param erpNo
     * @return
     */
    @Override
    public List<TProTecInfo> getProTecInfoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT tpti.id AS ID, tpti.subj_name AS SUBJ_NAME ");
        sqlbuilder.append(" FROM t_pro_tec_info tpti WHERE tpti.erp_no = ? AND tpti.del_sign = 0 AND tpti.is_valid = 0 ORDER BY tpti.acqu_time asc");
        return this.findAllBySQL(TProTecInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 绩效信息
     * @param erpNo
     * @return
     */
    @Override
    public List<TYearEffectInfo> getYearEffectInfoList(String erpNo, Integer year){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT tyei.id AS ID, tyei.perfor_grade AS PERFOR_GRADE, tyei.rate_year AS RATE_YEAR ");
        sqlbuilder.append(" FROM t_year_effect_info tyei WHERE tyei.erp_no = ? AND tyei.rate_year = ? AND tyei.del_sign = 0 AND tyei.is_valid = 0 ORDER BY tyei.rate_year desc ");
        return this.findAllBySQL(TYearEffectInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo,year});
    }

    /**
     * 简历
     * @param erpNo
     * @return
     */
    @Override
    public List<TWorkExpInfo> getWorkExpInfoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT twei.id AS ID, twei.start_date AS START_DATE, twei.end_date AS END_DATE, twei.company AS COMPANY, twei.dept AS DEPT, twei.post AS POST, twei.rank AS RANK ");
        sqlbuilder.append(" FROM t_work_exp_info twei WHERE twei.erp_no = ? AND twei.del_sign = 0 AND twei.is_valid = 0 ORDER BY twei.start_date asc");
        return this.findAllBySQL(TWorkExpInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 奖惩-个人荣誉表
     * @param erpNo
     * @return
     */
    @Override
    public List<THonorInfo> getHonorInfoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT thi.id AS ID, thi.acqu_time AS ACQU_TIME, thi.honor_title AS HONOR_TITLE, thi.award_org AS AWARD_ORG, thi.honor_level AS HONOR_LEVEL, thi.honor_type AS HONOR_TYPE ");
        sqlbuilder.append(" FROM t_honor_info thi WHERE thi.erp_no = ? AND thi.del_sign = 0 AND thi.is_valid = 0 ORDER BY thi.acqu_time asc");
        return this.findAllBySQL(THonorInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 技艺革新
     * @param erpNo
     * @return
     */
    @Override
    public List<TTecInnovInfo> getTecInnovInfoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT tii.id AS ID, tii.achi_name AS ACHI_NAME, tii.award_name AS AWARD_NAME, tii.award_date AS AWARD_DATE, tii.award_org AS AWARD_ORG ");
        sqlbuilder.append(" FROM t_tec_innov_info tii LEFT JOIN t_part_rank tpr on tpr.item_id=tii.id WHERE tpr.erp_no = ? AND tii.del_sign = 0 AND tii.is_valid = 0 ORDER BY tii.award_date ASC");
        return this.findAllBySQL(TTecInnovInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 科技及创新成果
     * @param erpNo
     * @return
     */
    @Override
    public List<TTecManInnov> getTecManInnovList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT tmi.id AS ID, tmi.achi_name AS ACHI_NAME, tmi.award_name AS AWARD_NAME, tmi.award_date AS AWARD_DATE, tmi.award_org AS AWARD_ORG ");
        sqlbuilder.append(" FROM t_tec_man_innov tmi LEFT JOIN t_part_rank tpr on tpr.item_id=tmi.id WHERE tpr.erp_no = ? AND tmi.del_sign = 0 AND tmi.is_valid = 0 ORDER BY tmi.award_date ASC");
        return this.findAllBySQL(TTecManInnov.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 专利论文
     * @param erpNo
     * @return
     */
    @Override
    public List<TPatentInfo> getPatentInfoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT pi.id AS ID, pi.patent_name AS PATENT_NAME, pi.patent_no AS PATENT_NO, pi.apply_date AS APPLY_DATE, pi.decl_date AS DECL_DATE ");
        sqlbuilder.append(" FROM t_patent_info pi LEFT JOIN t_part_rank tpr on tpr.item_id=pi.id WHERE tpr.erp_no = ? AND pi.del_sign = 0 AND pi.is_valid = 0 ORDER BY pi.apply_date ASC");
        return this.findAllBySQL(TPatentInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 竞赛考试信息
     * @param erpNo
     * @return
     */
    @Override
    public List<TContestInfo> getContestInfoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT ci.id AS ID, ci.award_date AS AWARD_DATE, ci.comp_name AS COMP_NAME, ci.host_org AS HOST_ORG, ci.award_name AS AWARD_NAME ");
        sqlbuilder.append(" FROM t_contest_info ci WHERE ci.erp_no = ? AND ci.del_sign = 0 AND ci.is_valid = 0 ORDER BY ci.award_date ASC");
        return this.findAllBySQL(TContestInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 技能等级
     * @param erpNo
     * @return
     */
    @Override
    public List<TSkillLevelInfo> getSkillLevelInfoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT sli.id AS ID, sli.acqu_time AS ACQU_TIME, sli.tech_type AS TECH_TYPE, sli.qualifi_level AS QUALIFI_LEVEL, sli.audit_org AS AUDIT_ORG, sli.cert_no AS CERT_NO ");
        sqlbuilder.append(" FROM t_skill_level_info sli WHERE sli.erp_no = ? AND sli.del_sign = 0 AND sli.is_valid = 0 ORDER BY sli.acqu_time ASC");
        return this.findAllBySQL(TSkillLevelInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 专家人才称号信息
     * @param erpNo
     * @return
     */
    @Override
    public List<TExpertTitleInfo> getExpertTitleInfoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT eti.id AS ID, eti.major AS MAJOR, eti.acqu_time AS ACQU_TIME, eti.level_name AS LEVEL_NAME, eti.emp_title AS EMP_TITLE ");
        sqlbuilder.append(" FROM t_expert_title_info eti WHERE eti.erp_no = ? AND eti.del_sign = 0 AND eti.is_valid = 0 ORDER BY eti.acqu_time asc");
        return this.findAllBySQL(TExpertTitleInfo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 专利论文
     * @param erpNo
     * @return
     */
    @Override
    public List<TPatentInfoVo> getPatentInfoVoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT pi.id AS ID, pi.patent_name AS PATENT_NAME, pi.patent_no AS PATENT_NO, pi.apply_date AS APPLY_DATE, pi.decl_date AS DECL_DATE," +
                "GET_DIC('PatentType',pi.patent_type) as PATENT_TYPE_NAME ");
        sqlbuilder.append(" FROM t_patent_info pi LEFT JOIN t_part_rank tpr on tpr.item_id=pi.id WHERE tpr.erp_no = ? AND pi.del_sign = 0 AND pi.is_valid = 0 ORDER BY pi.apply_date ASC");
        return this.findAllBySQL(TPatentInfoVo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }


    /**
     * 技艺革新
     * @param erpNo
     * @return
     */
    @Override
    public List<TTecInnovInfoVo> getTecInnovInfoVoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT tii.id AS ID, tii.achi_name AS ACHI_NAME, tii.award_name AS AWARD_NAME, tii.award_date AS AWARD_DATE, tii.award_org AS AWARD_ORG, " +
                "GET_DIC('JygexinQcJb',tii.award_level) as AWARD_LEVEL_NAME, GET_DIC('JygexinQcDj',tii.award_grade) as AWARD_GRADE_NAME ");
        sqlbuilder.append(" FROM t_tec_innov_info tii LEFT JOIN t_part_rank tpr on tpr.item_id=tii.id WHERE tpr.erp_no = ? AND tii.del_sign = 0 AND tii.is_valid = 0 ORDER BY tii.award_date ASC");
        return this.findAllBySQL(TTecInnovInfoVo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 科技及创新成果
     * @param erpNo
     * @return
     */
    @Override
    public List<TTecManInnovVo> getTecManInnovVoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT tmi.id AS ID, tmi.achi_name AS ACHI_NAME, tmi.award_name AS AWARD_NAME, tmi.award_date AS AWARD_DATE, tmi.award_org AS AWARD_ORG, " +
                "GET_DIC('JygexinQcDj',tmi.award_grade) as AWARD_GRADE_NAME, GET_DIC('KjglAchiveType',tmi.achi_type) as ACHI_TYPE_NAME ");
        sqlbuilder.append(" FROM t_tec_man_innov tmi LEFT JOIN t_part_rank tpr on tpr.item_id=tmi.id WHERE tpr.erp_no = ? AND tmi.del_sign = 0 AND tmi.is_valid = 0 ORDER BY tmi.award_date ASC");
        return this.findAllBySQL(TTecManInnovVo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }

    /**
     * 奖惩-个人荣誉表
     * @param erpNo
     * @return
     */
    @Override
    public List<THonorInfoVo> getHonorInfoVoList(String erpNo){
        StringBuilder sqlbuilder = new StringBuilder(" SELECT thi.id AS ID, thi.acqu_time AS ACQU_TIME, thi.honor_title AS HONOR_TITLE, thi.award_org AS AWARD_ORG, " +
                "thi.honor_level AS HONOR_LEVEL, thi.honor_type AS HONOR_TYPE, GET_DIC('JygexinQcJb',thi.honor_level) as HONOR_LEVEL_NAME, GET_DIC('RuYuLeiBie',thi.honor_type) as HONOR_TYPE_NAME ");
        sqlbuilder.append(" FROM t_honor_info thi WHERE thi.erp_no = ? AND thi.del_sign = 0 AND thi.is_valid = 0 ORDER BY thi.acqu_time asc");
        return this.findAllBySQL(THonorInfoVo.class, sqlbuilder.toString(), null, false, new Object[] { erpNo});
    }
}
